package com.yuxin.common.util;

import com.jolbox.bonecp.BoneCPConfig;
import com.jolbox.bonecp.BoneCPDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import javax.servlet.http.HttpServletRequest;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

/**
 * @author zehui.zeng
 * @date 13-3-17 下午9:43
 */
public class DBUtil {


    private static DataSource dataSource;// 数据源

    /***
     * 获取数据源
     * @return
     * @throws Exception
     */
    public static DataSource getDataSource() throws SQLException{
        if(dataSource != null){
            return dataSource;
        }
        try{
            Properties  properties = new Properties();
            properties.load(DBUtil.class.getClassLoader().getResourceAsStream("datasource.properties"));
            Class.forName(properties.getProperty("bonecp.driverClass"));
            String defaultAutoCommit = properties.getProperty("bonecp.defaultAutoCommit");
            String defaultReadOnly = properties.getProperty("bonecp.defaultReadOnly");
            BoneCPConfig config = new BoneCPConfig(properties);
            if(StringUtils.hasText(defaultAutoCommit)) {
                config.setDefaultAutoCommit(Boolean.valueOf(defaultAutoCommit));
            }
            if(StringUtils.hasText(defaultReadOnly)) {
                config.setDefaultReadOnly(Boolean.valueOf(defaultReadOnly));
            }
            //BoneCPDataSource boncp = new BoneCPDataSource(config);
           // dataSource = new DelegatingDataSource(boncp);
            dataSource = new BoneCPDataSource(config);
        }catch (Exception e){
            throw new SQLException(e);
        }
        return dataSource;
    }

    public static Connection getConnection() throws SQLException {
        Connection conn = getDataSource().getConnection();
        conn.setAutoCommit(true);
        return conn;
    }

    /**
     * 列表查询
     * @param sql
     * @param parameters
     * @return
     * @throws Exception
     */
    public static List<Map<String,Object>> queryList(Connection conn,String sql,Object...parameters) throws SQLException {
        List<Map<String,Object>> result = null;
        QueryRunner runner= new QueryRunner();
        MapListHandler handler = new MapListHandler();
        result = runner.query(conn,sql,handler,parameters);
        return result;
    }

    /***
     * 分页查询
     * @param conn
     * @param request
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static Map<String,Object> queryPage(Connection conn,HttpServletRequest request,String sql,Object...parameters) throws SQLException {
        int pageNum = 1;
        int pageSize = 15;
        if(StringUtils.hasText(request.getParameter("pageNum"))){
            pageNum = Integer.parseInt(request.getParameter("pageNum"));
        }
        if(StringUtils.hasText(request.getParameter("pageSize"))){
            pageSize = Integer.parseInt(request.getParameter("pageSize"));
        }
        int start = (pageNum - 1)*pageSize;
        return queryPage(conn,sql,start,pageSize,parameters);
    }

    /**
     * 分页查询
     * @param conn
     * @param sql
     * @param start
     * @param end
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static Map<String,Object> queryPage(Connection conn,String sql,int start,int pageSize,Object...parameters) throws SQLException {
        int count = DBUtil.count(conn,sql,parameters);
        List paramList = new ArrayList();
        List resultList = new ArrayList();
        Map result = new HashMap();
        for(Object obj : parameters){
            paramList.add(obj);
        }
        if(count > 0){
            paramList.add(start);
            paramList.add(pageSize);
            resultList = DBUtil.queryList(conn,sql+" LIMIT ?,? ",paramList.toArray());
        }
        result.put("count",count);
        result.put("list",resultList);
        return result;
    }


    /**
     * 查询唯一结果集
     * @param conn
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static Map<String,Object> queryUniqueResult(Connection conn,String sql,Object...parameters) throws SQLException {
        QueryRunner runner= new QueryRunner();
        MapHandler handler = new MapHandler();
        return runner.query(conn,sql,handler,parameters);
    }

    /***
     * 查询数据总行数
     * @param conn
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static int count(Connection conn,String sql,Object...parameters) throws SQLException {
        sql = "select count(0) COUNT from "+sql.replaceAll(".*?(?i)from\\s","").replaceAll("\\s(?i)order\\s*by\\s*(asc|desc)*$","");
        QueryRunner runner= new QueryRunner();
        MapHandler handler = new MapHandler();
        Map result = runner.query(conn,sql,handler,parameters);
        return result.get("COUNT") == null ? 0 : Integer.parseInt(result.get("COUNT").toString());
    }

    /**
     * 列表查询
     * @param sql
     * @param parameters
     * @return
     * @throws Exception
     */
    public static Map<String,Object> findById(Connection conn,String objectName,String id) throws SQLException {
        Map<String,Object> result = null;
        QueryRunner runner= new QueryRunner();
        MapHandler handler = new MapHandler();
        String sql = "SELECT * FROM "+objectName+" WHERE ID = ? ";
        result = runner.query(conn,sql,handler,id);
        return result;
    }

    /***
     * 按主键删除数据
     * @param conn
     * @param objectName
     * @param id
     * @return
     */
    public static int deleteById(Connection conn,String objectName,String id) throws SQLException {
        String sql = "DELETE FROM "+objectName+" WHERE ID = ? ";
        QueryRunner runner= new QueryRunner();
        return runner.update(conn,sql,id);
    }

    /***
     * SQL语句执行
     * @param sql
     * @param parameters
     * @return
     * @throws IOException
     * @throws SQLException
     */
    public static int executeSql(Connection conn,String sql,Object...parameters) throws SQLException {
        QueryRunner runner= new QueryRunner();
        return runner.update(conn,sql,parameters);
    }

    /***
     * 数据保存
     * @param conn
     * @param data
     * @param objectName
     * @return
     * @throws SQLException
     */
    public static int save(Connection conn,Map data,String objectName) throws SQLException {
    	String dateStr = DateUtil.fomatDate(new Date(), "yyyy-MM-dd HH:mm:ss");
   	 	data.put("UPDATE_TIME",dateStr);
   	 	data.put("CREATE_TIME",dateStr);
        Iterator it = data.keySet().iterator();
        String key = "";
        StringBuffer insertSql = new StringBuffer(128);
        insertSql.append("INSERT INTO ").append(objectName);
        List valueList = new ArrayList();
        List<String> filedNameList = new ArrayList<String>();
        List<String> preValueList = new ArrayList<String>();
        while (it.hasNext()){
            key = it.next().toString();
            filedNameList.add(key);
            preValueList.add("?");
            valueList.add(data.get(key));
        }
        insertSql.append(" ( ").append(StringUtils.collectionToDelimitedString(filedNameList,","))
                .append(" ) VALUES ( ").append(StringUtils.collectionToDelimitedString(preValueList,",")).append(")");
        QueryRunner run = new QueryRunner();
        return run.update(conn,insertSql.toString(),valueList.toArray());
    }

    /***
     * 数据更新
     * @param conn
     * @param data
     * @param objectName
     * @return
     * @throws SQLException
     */
    public static int update(Connection conn,Map data,String objectName) throws SQLException {
    	String dateStr = DateUtil.fomatDate(new Date(), "yyyy-MM-dd HH:mm:ss");
    	data.put("UPDATE_TIME",dateStr);
        Iterator it = data.keySet().iterator();
        String key = "";
        List valList = new ArrayList();
        StringBuffer updateSql = new StringBuffer(128);
        updateSql.append("UPDATE ").append(objectName).append(" SET ");
        while (it.hasNext()){
            key = it.next().toString();
            if(key.equalsIgnoreCase("ID")){
            	continue;
            }
            updateSql.append(key).append("=?,");
            valList.add(data.get(key));
        }
        updateSql.deleteCharAt(updateSql.length() - 1);
        updateSql.append(" WHERE ID= ? ");
        valList.add(data.get("ID"));
        return executeSql(conn, updateSql.toString(),valList.toArray());
    }

    /**
     * Close a <code>Connection</code>, avoid closing if null.
     *
     * @param conn Connection to close.
     * @throws java.sql.SQLException if a database access error occurs
     */
    public static void close(Connection conn) {
        if (conn != null) {
            try {
                if(!conn.isClosed()){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}
